package zy.dao.report.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.report.EmpReportDAO;
import zy.dto.base.emp.EmpSortDto;

@Repository
public class EmpReportDAOImpl extends BaseDaoImpl implements EmpReportDAO{
	
	@Override
	public Integer queryEmpSort(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT rownum FROM(");
		sql.append(" SELECT temp.*,");
		sql.append(" @rownum:=@rownum+1 AS rownum ");
		sql.append(" FROM");
		sql.append(" (SELECT em_code,");
		sql.append(" IFNULL((SELECT SUM(shl_money) ");
		sql.append(" FROM t_sell_shoplist shl ");
		sql.append(" WHERE shl_main = em_code");
		sql.append(" AND shl.companyid = t.companyid");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND shl_shop_code = :shop_code");
		sql.append(" ),0) AS money");
		sql.append(" FROM t_base_emp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND em_state = 0");
		sql.append(" AND em_type = 0");
		sql.append(" AND em_shop_code = :shop_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY money DESC");
		sql.append(" ) temp,(SELECT @rownum:=0) r");
		sql.append(" ) s");
		sql.append(" WHERE em_code = :em_code");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}
	
	@Override
	public List<EmpSortDto> listEmpSort(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT em_id,em_code,em_name,");
		sql.append(" IFNULL((SELECT SUM(shl_money) ");
		sql.append(" FROM t_sell_shoplist shl ");
		sql.append(" WHERE shl_main = em_code");
		sql.append(" AND shl.companyid = t.companyid");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND shl_shop_code = :shop_code");
		sql.append(" ),0) AS sellmoney,");
		sql.append(" (SELECT COUNT(1) ");
		sql.append(" FROM t_sell_receive re ");
		sql.append(" WHERE re_em_code = em_code");
		sql.append(" AND re.companyid = t.companyid");
		sql.append(" AND re_date >= :begindate");
		sql.append(" AND re_date <= :enddate");
		sql.append(" AND re_shop_code = :shop_code");
		sql.append(" ) AS receivecount");
		sql.append(" FROM t_base_emp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND em_state = 0");
		sql.append(" AND em_type = 0");
		sql.append(" AND em_shop_code = :shop_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY sellmoney DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(EmpSortDto.class));
	}
	
	@Override
	public List<EmpSortDto> listEmpDealCount(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT em_code,SUM(dealcount) AS dealcount"); 
		sql.append(" FROM(");
		sql.append(" SELECT shl_main AS em_code,shl_number,");
		sql.append(" (CASE WHEN shl_state = 0 THEN 1 WHEN SHL_State = 1 THEN -1 END )AS dealcount");
		sql.append(" FROM t_sell_shoplist t ");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND shl_shop_code = :shop_code");
		sql.append(" AND shl_state IN (0,1)");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY shl_main,shl_number");
		sql.append(" )temp");
		sql.append(" GROUP BY em_code");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(EmpSortDto.class));
	}
	
	@Override
	public Map<String, Object> querySellByEmp(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" SUM(shl_amount) AS shl_amount,");
		sql.append(" SUM(shl_money) AS shl_money,");
		sql.append(" SUM(shl_amount * shl_sell_price) AS shl_sellmoney");
		sql.append(" FROM t_sell_shoplist");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND shl_main = :em_code");
		sql.append(" AND shl_shop_code = :shop_code");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}
	@Override
	public Map<String, Object> queryDealCountByEmp(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" SUM(IF(t.shl_state = 0,dealcount,0)) AS sell_count,");
		sql.append(" SUM(IF(t.shl_state = 1,dealcount,0)) AS return_count");
		sql.append(" FROM");
		sql.append(" (");
		sql.append(" SELECT shl_state,COUNT(1) AS dealcount");
		sql.append(" FROM");
		sql.append(" (");
		sql.append(" SELECT shl_state");
		sql.append(" FROM t_sell_shoplist");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_state IN(0,1)");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND shl_main = :em_code");
		sql.append(" AND shl_shop_code = :shop_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" GROUP BY shl_number");
		sql.append(" )temp");
		sql.append(" GROUP BY temp.shl_state");
		sql.append(" )t");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}
	
	@Override
	public Integer countCome(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SUM(da_come)");
		sql.append(" FROM t_sell_day");
		sql.append(" WHERE 1=1");
		sql.append(" AND da_date >= :begindate");
		sql.append(" AND da_date <= :enddate");
		sql.append(" AND da_shop_code = :shop_code");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}
	
	@Override
	public Integer countReceiveByEmp(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1)");
		sql.append(" FROM t_sell_receive");
		sql.append(" WHERE 1=1");
		sql.append(" AND re_date >= :begindate");
		sql.append(" AND re_date <= :enddate");
		sql.append(" AND re_shop_code = :shop_code");
		sql.append(" AND re_em_code = :em_code");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}
	
	@Override
	public Integer countTryByEmp(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1)");
		sql.append(" FROM t_sell_try");
		sql.append(" WHERE 1=1");
		sql.append(" AND tr_date >= :begindate");
		sql.append(" AND tr_date <= :enddate");
		sql.append(" AND tr_shop_code = :shop_code");
		sql.append(" AND tr_em_code = :em_code");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}
	@Override
	public Integer countVipByEmp(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1)");
		sql.append(" FROM t_vip_member");
		sql.append(" WHERE 1=1");
		sql.append(" AND vm_sysdate >= :begindate");
		sql.append(" AND vm_sysdate <= :enddate");
		sql.append(" AND vm_manager_code = :em_code");
		sql.append(" AND vm_shop_code = :shop_code");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

}
